package gsdb

import (
	"database/sql"
	"errors"
	"fmt"
	"gitee.com/Sxiaobai/gs/gsdefine"
	"gitee.com/Sxiaobai/gs/gstool"
	"github.com/spf13/cast"
	"runtime/debug"
	"strings"
)

const (
	Query = iota
	Insert
	Update
	Delete
	Exec //仅返回受影响行数 如果是插入 需要使用Insert
)

type SqlQuick struct {
	db     *sql.DB
	format *SqlFormat
	sql    string //准备执行的sql
	op     int    //操作 create  query  update  delete
	params []any  //参数
	err    error
	debug  bool //是否输出sql 将会在ExecBySql InsertBySql QueryBySql输出完整sql
	GsLog  *gstool.GsSlog
	limit  bool
	dbType string //mysql sqlite pgsql
}

// Limit 操作多条
func (h *SqlQuick) Limit(limit int) *SqlQuick {
	if h.err != nil {
		return h
	}
	if h.sql == `` {
		h.err = errors.New(`请在执行Quick系列函数之后再执行Limit方法`)
		return h
	}
	h.limit = true
	h.sql = h.sql + ` limit ` + cast.ToString(limit)
	return h
}

func (h *SqlQuick) OffsetLimit(offset, limit int) *SqlQuick {
	if h.err != nil {
		return h
	}
	if h.sql == `` {
		h.err = errors.New(`请在执行Quick系列函数之后再执行Where方法`)
		return h
	}
	h.limit = true
	h.sql = h.sql + ` limit ` + cast.ToString(offset) + `,` + cast.ToString(limit)
	return h
}

func (h *SqlQuick) Order(orderSql string) *SqlQuick {
	if h.err != nil {
		return h
	}
	if h.sql == `` {
		h.err = errors.New(`请在执行Quick系列函数之后再执行Order方法`)
		return h
	}
	h.sql = h.sql + ` order by ` + orderSql
	return h
}

func (h *SqlQuick) setTemp(sql string, params []any, op int, err error) {
	h.sql = sql
	//处理空数据
	if len(params) == 1 && params[0] == nil {
		h.params = make([]any, 0)
	} else {
		h.params = params
	}
	h.op = op
	h.err = err
}

func (h *SqlQuick) QuickQuery(tableName, fields string, where map[string]interface{}) *SqlQuick {
	valueList := make([]any, 0)
	sqlWhere, formatErr := h.format.FormatQuery(tableName, where, &valueList)
	if formatErr != nil {
		h.setTemp(``, nil, Query, formatErr)
		return h
	}
	sqlExec := fmt.Sprintf(`select %s from %s %s `, fields, tableName, sqlWhere)
	h.setTemp(sqlExec, valueList, Query, nil)
	return h
}

// QuickUpdate 快速更新
func (h *SqlQuick) QuickUpdate(tableName string, where map[string]interface{}, update map[string]interface{}) *SqlQuick {
	if where == nil || len(where) == 0 {
		h.err = errors.New(`where条件不能为空`)
		return h
	}
	valueList := make([]any, 0)
	sqlUpdate, formatErr := h.format.FormatUpdate(tableName, update, &valueList)
	if formatErr != nil {
		h.setTemp(``, nil, Update, formatErr)
		return h
	}
	sqlWhere, formatErr := h.format.FormatQuery(tableName, where, &valueList)
	if formatErr != nil {
		h.setTemp(``, nil, Update, formatErr)
		return h
	}
	sqlExec := fmt.Sprintf(`update %s set %s %s`, tableName, sqlUpdate, sqlWhere)
	h.setTemp(sqlExec, valueList, Update, nil)
	return h
}

// QuickDelete 快速删除数据
func (h *SqlQuick) QuickDelete(tableName string, where map[string]interface{}) *SqlQuick {
	valueList := make([]any, 0)
	sqlWhere, formatErr := h.format.FormatQuery(tableName, where, &valueList)
	if formatErr != nil {
		h.setTemp(``, nil, Delete, formatErr)
		return h
	}
	sqlExec := fmt.Sprintf(`delete from %s %s `, tableName, sqlWhere)
	h.setTemp(sqlExec, valueList, Delete, nil)
	return h
}

// One 查询
func (h *SqlQuick) One() (map[string]any, error) {
	if h.err != nil {
		return nil, h.err
	}
	if h.sql == `` {
		return nil, errors.New(`请先调用QuickQuery系列方法`)
	}
	if h.op != Query {
		return nil, errors.New(`one仅支持quick query系列方法`)
	}
	if !h.limit {
		h.Limit(1)
	}
	dataList, err := h._queryBySql(h.sql, h.params...)
	if err != nil {
		return nil, err
	}
	dataLength := len(dataList)
	if dataLength >= 1 {
		return dataList[0], nil
	} else {
		return make(map[string]any), nil
	}
}

// All 操作多条
func (h *SqlQuick) All() ([]map[string]any, error) {
	if h.err != nil {
		return nil, h.err
	}
	if h.sql == `` {
		return nil, errors.New(`请先调用QuickQuery方法`)
	}
	if h.op != Query {
		return nil, errors.New(`all仅支持quick query系列方法`)
	}
	return h._queryBySql(h.sql, h.params...)
}

// Exec 执行变更
func (h *SqlQuick) Exec() (int64, error) {
	if h.err != nil {
		return 0, h.err
	}
	if h.sql == `` {
		return 0, errors.New(`请先调用QuickDelete QuickUpdate  QuickCreate方法`)
	}
	allowOpList := []int{Update, Delete, Insert, Exec}
	if !gstool.ArrayExistValue(&allowOpList, h.op) {
		return 0, errors.New(`exec仅支持quick update delete insert系列方法`)
	}
	if h.op == Delete || h.op == Update || h.op == Exec {
		return h._execBySql(h.sql, h.params...)
	} else {
		return h._insertBySql(h.sql, h.params...)
	}
}

func (h *SqlQuick) Debug() *SqlQuick {
	h.debug = true
	return h
}

func (h *SqlQuick) GetSql(sql *string) *SqlQuick {
	*sql = h._getSql(h.sql, h.params)
	return h
}

// debugSql 获取sql
func (h *SqlQuick) debugSql(sql string, params []any) {
	if !h.debug && h.err == nil {
		return
	}
	sql = h._getSql(sql, params)
	h.Debugf(`Sql :%s `, sql)
}

func (h *SqlQuick) _getSql(sql string, params []any) string {
	placeholderNumErr := h.format.checkReplacePlaceholderNum(sql, params)
	if placeholderNumErr != nil {
		sql = placeholderNumErr.Error()
	} else {
		if h.dbType == DbTypePgsql {
			sql = gstool.StringReplaceRegex(sql, `\$\d+`, `%s`)
		} else {
			sql = strings.ReplaceAll(sql, `?`, `%s`)
		}
		replaceValues := make([]any, 0)
		for _, value := range params {
			if gstool.ReflectIsString(value) {
				replaceValues = append(replaceValues, `'`+cast.ToString(value)+`'`)
			} else {
				replaceValues = append(replaceValues, cast.ToString(value))
			}
		}
		sql = fmt.Sprintf(sql, replaceValues...)
	}
	return sql
}

// QueryBySql 获取多行数据
func (h *SqlQuick) QueryBySql(sqlStr string, params ...interface{}) *SqlQuick {
	h.setTemp(sqlStr, params, Query, nil)
	return h
}

// QueryBySql 获取多行数据 最终的查询
func (h *SqlQuick) _queryBySql(sqlStr string, params ...interface{}) ([]map[string]any, error) {
	var rows *sql.Rows
	var err error
	var dataList = make([]map[string]any, 0)
	h.debugSql(sqlStr, params)
	rows, err = h.db.Query(sqlStr, params...)
	if err != nil {
		return dataList, err
	}
	defer func(rows *sql.Rows) {
		errClose := rows.Close()
		if errClose != nil {
			h.Errof(`关闭查询失败 %s`+gsdefine.Enter+` %s `+gsdefine.Enter+`%s`+gsdefine.Enter+`%#v`, err.Error(), sqlStr, debug.Stack(), params)
		}
	}(rows)
	var columns []string
	columns, err = rows.Columns()
	if err != nil {
		return dataList, err
	}
	values := make([]interface{}, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	// 这里需要初始化为空数组，否则在查询结果为空的时候，返回的会是一个未初始化的指针
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		if err != nil {
			return dataList, err
		}
		dataRow := make(map[string]any)
		for i, col := range values {
			if b, ok := col.([]byte); ok {
				dataRow[columns[i]] = string(b) // 如果是 []byte，强制转 string
			} else {
				dataRow[columns[i]] = col
			}
		}
		dataList = append(dataList, dataRow)
	}
	if err = rows.Err(); err != nil {
		return dataList, err
	}
	return dataList, nil
}

// ExecBySql 执行
func (h *SqlQuick) ExecBySql(sqlStr string, params ...interface{}) *SqlQuick {
	h.setTemp(sqlStr, params, Exec, nil)
	return h
}

// ExecBySql 执行
func (h *SqlQuick) _execBySql(sqlStr string, params ...interface{}) (int64, error) {
	h.debugSql(sqlStr, params)
	ret, err := h.db.Exec(sqlStr, params...)
	if err != nil {
		return 0, err
	}
	rows, err := ret.RowsAffected()
	if err != nil {
		return 0, err
	}
	return cast.ToInt64(rows), nil
}

// InsertBySql 插入
func (h *SqlQuick) InsertBySql(sqlStr string, params ...interface{}) *SqlQuick {
	h.setTemp(sqlStr, params, Insert, nil)
	return h
}

// InsertBySql 插入
func (h *SqlQuick) _insertBySql(sqlStr string, params ...interface{}) (int64, error) {
	h.debugSql(sqlStr, params)
	ret, err := h.db.Exec(sqlStr, params...)
	if err != nil {
		return 0, err
	}

	lastId, err := ret.LastInsertId() // 新插入的数据id
	if err != nil {
		return 0, err
	}
	return lastId, nil
}

func (h *SqlQuick) QuickCreate(tableName string, params map[string]interface{}) *SqlQuick {
	sqlField, sqlQues, valueList, formatErr := h.format.FormatInsert(tableName, params)
	if formatErr != nil {
		h.setTemp(``, nil, Insert, formatErr)
		return h
	}
	sqlExec := fmt.Sprintf(`insert into %s (%s) values(%s)`, tableName, sqlField, sqlQues)
	h.setTemp(sqlExec, valueList, Insert, nil)
	return h
}

// TableColumnsMap 查询表的所有列 map[string]string
func (h *SqlQuick) TableColumnsMap(columnList []map[string]any) (map[string]string, error) {
	tableFieldMap := map[string]string{}
	for _, dbFieldTypeParam := range columnList {
		if h.dbType == DbTypeMysql {
			tableFieldMap[cast.ToString(dbFieldTypeParam[`COLUMN_NAME`])] = cast.ToString(dbFieldTypeParam[`DATA_TYPE`])
		} else if h.dbType == DbTypePgsql {
			tableFieldMap[cast.ToString(dbFieldTypeParam[`column_name`])] = cast.ToString(dbFieldTypeParam[`data_type`])
		} else if h.dbType == DbTypeSqlite {
			tableFieldMap[cast.ToString(dbFieldTypeParam[`name`])] = cast.ToString(dbFieldTypeParam[`type`])
		}
	}
	return tableFieldMap, nil
}

// TableDetail 查询表的信息
func (h *SqlQuick) TableDetail(tableName string) ([]map[string]any, error) {
	sqlStr := fmt.Sprintf(`SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE () AND TABLE_NAME = '%s'`, tableName)
	return h.QueryBySql(sqlStr, nil).All()
}

func (h *SqlQuick) Debugf(msg string, params ...interface{}) {
	if h.GsLog != nil {
		h.GsLog.Debugf(msg, params...)
	} else {
		gstool.FmtPrintlnLog(msg, params...)
	}
}

func (h *SqlQuick) Errof(msg string, params ...interface{}) {
	if h.GsLog != nil {
		h.GsLog.Debugf(msg, params...)
	} else {
		gstool.FmtPrintlnLog(msg, params...)
	}
}
